
import xlrd
import xlwt
import os
from datetime import time, datetime
import argparse


def get_records(book, month_number):
    records = {}
    sheet = book.sheet_by_index(0)
    for row_index in range(1, sheet.nrows):
        name = sheet.cell_value(row_index, 1)
        time = sheet.cell_value(row_index, 2)
        date_time = xlrd.xldate.xldate_as_datetime(time, book.datemode)
        if date_time.date().month != month_number:
            continue
        if name not in records:
            records[name] = []
        records[name].append(time)
    return records


def collate_records(records, book):
    for name, times in records.items():
        checkin_table = {}
        for check_time in times:
            date_time = xlrd.xldate.xldate_as_datetime(check_time, book.datemode)
            date_part = date_time.date()
            if date_part not in checkin_table:
                checkin_table[date_part] = []
            checkin_table[date_part].append(date_time)

        for key, value in checkin_table.items():
            if len(value) > 2:
                new_checkins = []
                new_checkins.append(value[0])
                new_checkins.append(value[-1])
                checkin_table[key] = new_checkins

        records[name] = checkin_table


def create_new_book(records):
    date_style = xlwt.easyxf(num_format_str='YYYY-MM-DD')
    warn_date_style = xlwt.easyxf('font: color-index red', num_format_str='YYYY-MM-DD')
    normal_time_style = xlwt.easyxf(num_format_str='hh:mm:ss')
    warn_time_style = xlwt.easyxf('font: color-index red', num_format_str='hh:mm:ss')
    header_style = xlwt.easyxf('font: bold on')
    begin_work_time = time(9, 30, 0)
    end_work_time = time(18, 0, 0)
    middle_time = time(12, 0, 0)

    new_book = xlwt.Workbook(encoding='utf-8')
    new_sheet = new_book.add_sheet('sheet')
    new_sheet.write(0, 0, '姓名', header_style)
    new_sheet.write(0, 1, '日期', header_style)
    new_sheet.write(0, 2, '签到时间', header_style)
    new_sheet.write(0, 3, '签退时间', header_style)
    row_index = 1
    for name, check_ins in records.items():
        for date, times in check_ins.items():
            new_sheet.write(row_index, 0, name)
            if len(times) == 1:
                new_sheet.write(row_index, 1, date, date_style)
                col_index = 2 if times[0].time() < middle_time else 3
                new_sheet.write(row_index, col_index, times[0].time(), warn_time_style)
            elif len(times) == 2:
                if (times[1] - times[0]).total_seconds() >= (3600 * 9.5):
                    new_sheet.write(row_index, 1, date, date_style)
                else:
                    new_sheet.write(row_index, 1, date, warn_date_style)
                if times[0].time() > begin_work_time:
                    new_sheet.write(row_index, 2, times[0].time(), warn_time_style)
                else:
                    new_sheet.write(row_index, 2, times[0].time(), normal_time_style)
                if times[1].time() < end_work_time:
                    new_sheet.write(row_index, 3, times[1].time(), warn_time_style)
                else:
                    new_sheet.write(row_index, 3, times[1].time(), normal_time_style)
            row_index += 1

    return new_book


def get_new_file_name(origin_file_path, month):
    return 'D:\\Report_Month' + str(month) + '_Generated_' + datetime.now().strftime('%H-%M-%S') + '.xls'


def main(args):
    file_name_path = os.path.splitext(args.OriginFilePath)[0]
    book = xlrd.open_workbook(os.extsep.join([file_name_path, 'xls']))
    records = get_records(book, args.Month)
    collate_records(records, book)

    new_book = create_new_book(records)
    new_book.save(get_new_file_name(file_name_path, args.Month))


if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument('OriginFilePath', type=str, help='File to be handle')
    parser.add_argument('Month', type=int, help='Month number')
    args = parser.parse_args()
    main(args)
